package com.servlet;

import com.bean.BhRole;
import com.bean.BhUser;
import com.dao.BhRoleDao;
import com.dao.BhUserDao;
import com.dao.impl.BhRoleDaoImpl;
import com.dao.impl.BhUserDaoImpl;
import com.util.DB;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * 用户Servlet
 */
@WebServlet("/user/*")
public class BhUserServlet extends  BaseServlet{
    BhUserDao bhu= new BhUserDaoImpl();//用户方法
    BhRoleDao bhr=new BhRoleDaoImpl();
    Connection conn = DB.getConn();
    /**
     * 实现登录进行跳转
     */
    public void register(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        System.out.println("register运行");
        String uName=request.getParameter("name");//账号名称
        String pass=request.getParameter("pass");
        if (uName==null||uName==""||pass==null||pass==""){
            response.sendRedirect("../BhSyCaseJoin.jsp");
        }
       int uID=Integer.valueOf(pass);//账号密码
        System.out.println("wqe"+uName+","+pass);
        try {
            BhUser list=bhu.sel(uName,uID);
            System.out.println(list);
            if(list!=null){
                HttpSession session=request.getSession();
                System.out.println(list.getuSystem());
                List<BhRole> roles=bhr.selqx(list.getuSystem());
                System.out.println(roles);
                session.setAttribute("roles",roles);
                request.getRequestDispatcher("../index.jsp").forward(request,response);//跳转首页
            }else{
                response.sendRedirect("../BhSyCaseJoin.jsp");
//                request.getRequestDispatcher("../BhSyCaseJoin.jsp").forward(request,response);//跳转首页
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    /**
     * 读取excel实现用户注册
     */
    protected void add(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        DiskFileItemFactory factory = new DiskFileItemFactory();//
        ServletFileUpload upload = new ServletFileUpload(factory);
        upload.setHeaderEncoding("utf-8");//转编码
        //项目发布位置
        String appath = request.getServletContext().getRealPath("/doc");
        System.out.println(appath);
        try {
            List<FileItem> list = upload.parseRequest(request);
            //存盘
            for (FileItem fi : list) {
                if (fi.isFormField()) {
                    //文本框
                    String name = fi.getFieldName();
                    String value = fi.getString("utf-8");
                    System.out.println(name + "朋友圈" + value);
                } else {
                    //文件
//                    File file1=new File(appath+"/"+fi.getName());
//                    fi.write(file1);
                    String file = appath + "/" + fi.getName();
//                    String realpath = request.getRealPath(file);
//                    System.out.println(realpath);
                    InputStream input = new FileInputStream(file);
                    System.out.println(input);
                    XSSFWorkbook sheets = new XSSFWorkbook(input);
                    //XSSFSheet sheet = sheets.getSheet("Sheet1");  // 根据标签去第几个表格
                    XSSFSheet sheet = sheets.getSheetAt(0);
                    //获取行数
                    int rows = sheet.getPhysicalNumberOfRows();
                    XSSFRow row = sheet.getRow(0);
                    int cols = row.getPhysicalNumberOfCells();
                    System.out.println("文件多少行" + rows);
                    System.out.println("文件多少列" + cols);
                    String name1 ="";

                    try {
                        BhUser list2=null;
                        for(int i=1;i<rows;i++) {
                            XSSFRow row1 = sheet.getRow(i);
                            name1 = row1.getCell(1).toString();//取出名字
                            System.out.println(name1);
                            list2=list2= bhu.selname(name1);//查询
                            if(list2!=null){//判断存在后结束
                                break;
                            }
                        }
                        if(list2==null){//判断是否为空//空则添加
                            System.out.println("添加成功");
                            String uClass= "";
                            String uName= "";
                            String uSystem= "";
                            String sql = "insert into BhUser(uClass,uName,uSystem) values (?,?,?)";
                            PreparedStatement pstmt = DB.getPstmt(conn, sql);
                            BufferedInputStream in = null;
                            try {
                                in = new BufferedInputStream(new FileInputStream(file));
                            } catch (FileNotFoundException e1) {
                                e1.printStackTrace();
                            }
                            XSSFWorkbook workbook = null;
                            try {
                                workbook = new XSSFWorkbook(in);
                            } catch (IOException e) {
                                e.printStackTrace();
                            }
                            //HSSFSheet sheet = workbook.getSheetAt(0);

                            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                                row = sheet.getRow(rowIndex);
                                if (row == null) {
                                    continue;
                                }
                                XSSFCell cell = null;
                                for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
                                    String value = "";
                                    cell = row.getCell(columnIndex);
                                    if (cell != null) {
                                        // 注意：一定要设成这个，否则可能会出现乱码
                                        // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                                        switch (cell.getCellType()) {
                                            case XSSFCell.CELL_TYPE_STRING:
                                                value = cell.getStringCellValue();
                                                break;
                                            case XSSFCell.CELL_TYPE_NUMERIC:
                                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                                    Date date = cell.getDateCellValue();
                                                    if (date != null) {
                                                        value = new SimpleDateFormat("yyyy-MM-dd")
                                                                .format(date);
                                                    } else {
                                                        value = "";
                                                    }
                                                } else {
                                                    value = new DecimalFormat("0").format(cell
                                                            .getNumericCellValue());
                                                }
                                                break;
                                            case XSSFCell.CELL_TYPE_FORMULA:
                                                // 导入时如果为公式生成的数据则无值
                                                if (!cell.getStringCellValue().equals("")) {
                                                    value = cell.getStringCellValue();
                                                } else {
                                                    value = cell.getNumericCellValue() + "";
                                                }
                                                break;
                                            case XSSFCell.CELL_TYPE_BLANK:
                                                break;
                                            case XSSFCell.CELL_TYPE_ERROR:
                                                value = "";
                                                break;
                                            case XSSFCell.CELL_TYPE_BOOLEAN:
                                                value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                                                break;
                                            default:
                                                value = "";
                                        }
                                    }
                                    if (columnIndex == 0 && value.trim().equals("")) {
                                        break;
                                    }
                                    //excel表格中字段顺序为:用户名,密码,电话和地址,为方便起见假设字段一一对应
                                    if (columnIndex == 0) {
                                        uClass = value;
                                    } else if (columnIndex == 1) {
                                        uName = value;
                                    } else if (columnIndex == 2) {
                                        uSystem = value;
                                    }
                                    System.out.println(value);
                                }
                                try {
                                    pstmt.setString(1,uClass);
                                    pstmt.setString(2,uName);
                                    pstmt.setString(3, uSystem);
                                    pstmt.executeUpdate();
                                } catch (SQLException e) {
                                    e.printStackTrace();
                                }
                            }
                            try {
                                in.close();
                            } catch (IOException e) {
                                e.printStackTrace();
                            }
                            DB.closePstmt(pstmt);

                        }else{
                            System.out.println("添加失败");
                        }

                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }

                }
            }


        } catch (FileUploadException e) {
            e.printStackTrace();
        }
    }
}
